package cn.echoedu.daoimpl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import cn.echoedu.dao.DAOFactory;
import cn.echoedu.dao.DAORead;
import cn.echoedu.daoconstants.QueryType;
import cn.echoedu.daoconstants.TableName;
import cn.echoedu.entity.TelContacts;
import cn.echoedu.specifics.SpecMySql;
import cn.echoedu.util.DbUtil;
import cn.echoedu.util.L;

public class MySqlRead implements DAORead{
	private final String T="MySqlRead";

	DAOFactory fac=null;
	protected MySqlRead(DAOFactory fac) throws SQLException{
		this.fac=fac;
	}
	/**
	 * ��ѯһ������������
	 * �����������Ķ�
	 */
	public <T> List<T> getAll(Connection con, TableName tableName)
			throws SQLException {
		// TODO Auto-generated method stub
		List<T> list=new ArrayList<T>();
		PreparedStatement ps=null;
		ResultSet rs=null;
		try{
			ps=con.prepareStatement("select * from "+tableName);
			rs=ps.executeQuery();
			L.p("mysql:"+ps.toString());
			con.commit();
			while(rs.next()){
				list.add((T)SpecMySql.getPojoFromResultSet(tableName,rs));
			}
		}catch(Throwable e){  
			if(con!=null){  
				try {  
					DbUtil.rollback(con); 
				} catch (SQLException e1) {  
					e1.printStackTrace();  
				}  
			}  }
		finally{
			DAOFactory.closeAll(con,ps, rs);
		}
		return list;
	}
	/**
	 * ͨ������ֵ��ѯ
	 * �����������Ķ�
	 */
	public <T> List<T> getAllForInput(Connection con, TableName tableName,
			String columnName, String searchValue) throws SQLException {
		// TODO Auto-generated method stub
		List<T> list=new ArrayList<T>();
		PreparedStatement ps=null;
		ResultSet rs=null;
		try{
			ps=con.prepareStatement("SELECT * FROM "+tableName+" WHERE "+columnName
					+" ='"+searchValue+"'");
			L.p("mysql:"+ps.toString());
			rs=ps.executeQuery();
			con.commit();
			while(rs.next()){
				list.add((T)SpecMySql.getPojoFromResultSet(tableName,rs));
			}
		}catch(Throwable e){  
	           if(con!=null){  
	                DbUtil.rollback(con);
	           }  
		}finally{
			DAOFactory.closeAll(con,ps, rs);
		}
		return list;
	}

	public<T> List<T> getPojoForLeftJoinAndColumn(Connection con,TableName lefttablename,
			TableName righttablename,String leftcolumn,String rightcolumn,
			String column,String value,String orderbysql) 
					throws SQLException{
		final String M="getPojoForLeftJoinAndColumn***********>>";
		List<T> list=new ArrayList<T>();
		PreparedStatement ps=null;
		ResultSet rs=null;
		String sql="select * from "+lefttablename+" left join "+righttablename+
				" on ("+lefttablename+"."+leftcolumn+"="+righttablename+"."+rightcolumn+")" +
				" where "+column+"='"+value+"' "+orderbysql;
		L.p(T+M+sql);
		try{
			ps=con.prepareStatement(sql);
			L.p("mysql:"+ps.toString());
			rs=ps.executeQuery();
			con.commit();
			 
			while(rs.next()){
				list.add((T)SpecMySql.getPojoFromLeftResultSet(lefttablename,rs));
			}
		}catch(Throwable e){  
	           if(con!=null){  
	               try {  
	                  DbUtil.rollback(con);  
	               } catch (SQLException e1) {  
	                   e1.printStackTrace();  
	               }  
	           }  
		}finally{
			DAOFactory.closeAll(con,ps, rs);
		}
		return list;
	}

	/**
	 * ͨ��sql�����в�ѯ
	 * �����������Ķ�
	 */
	public<T> List<T> getPojoForSql(Connection con,TableName tablename,String sql) 
			throws SQLException{
		List<T> list=new ArrayList<T>();
		PreparedStatement ps=null;
		ResultSet rs=null;
		try{
			ps=con.prepareStatement(sql);
			L.p("mysql:"+ps.toString());
			rs=ps.executeQuery();
			con.commit();
			while(rs.next()){
				list.add((T)SpecMySql.getPojoBySql(tablename,rs));
			}
			System.out.println(list.toString()+"<<<<<<<//-----------");
		}catch(Throwable e){  
	           if(con!=null){  
	               try {  
	                   DbUtil.rollback(con);
	               } catch (SQLException e1) {  
	                   e1.printStackTrace();  
	               }  
	           }  
		}finally{
			DAOFactory.closeAll(con,ps, rs);
		}
		return list;
	}
	/**
	 * �����������Ķ�
	 */
	public <T> T getPojoForPrimarKey(Connection con, TableName tableName,
			String primaryKey) throws SQLException {
		// TODO Auto-generated method stub
		L.p(T+"getPojoForPrimaryKey");
		T currentPojo=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		try{
			String queryString=SpecMySql.queryString(tableName, primaryKey, QueryType.READ);
			ps=con.prepareStatement(queryString);
			L.p("mysql:"+ps.toString());
			rs=ps.executeQuery();
			con.commit();
			if(rs.next()){
				L.p("queryResult:"+rs);
				currentPojo=SpecMySql.getPojoFromResultSet(tableName, rs);
			}
		}catch(Throwable e){  
	           if(con!=null){  
	               try {  
	                  DbUtil.rollback(con); 
	               } catch (SQLException e1) {  
	                   e1.printStackTrace();  
	               }  
	           }  
		}finally{
			DAOFactory.closeAll(con,ps, rs);
		}
		L.p(T+"return currentPojo"+currentPojo);
		return currentPojo;
	}
	/**
	 * �����������Ķ�
	 */
	public <T> boolean alreadyExisting(Connection con, TableName tableName,
			String primaryKey) throws SQLException {
		L.p(T+"alreadyExisting1111*******>>");
		// TODO Auto-generated method stub
		if(getPojoForPrimarKey(con,tableName,primaryKey)!=null){
			return true;
		}else{
			return false;
		}

	}

	/**
	 * �����������Ķ�
	 */
	public <T> boolean alreadyExisting(Connection con, TableName tableName,
			T currentPojo) throws SQLException {
		// TODO Auto-generated method stub
		final String M="alreadExisting**********>>";
		L.p(T+M);
		String primaryKey=SpecMySql.<T>getPrimaryKey(tableName,currentPojo);

		if(!alreadyExisting(con,tableName,primaryKey)){
			L.p(T+M+"no exists");
			return false;
		}else{
			L.p(T+M+"exists");
			return true;

		}

	}
	public int getCount(Connection connection, TableName tablename
			) {
		// TODO Auto-generated method stub
		PreparedStatement ps=null;
		ResultSet rs=null;
		String sql = "select count(*) from "+tablename;
		L.p("getCountsql:"+sql);
		int num = 0;
		try{
			ps=connection.prepareStatement(sql);
			L.p("mysql:"+ps.toString());
			rs=ps.executeQuery();
			connection.commit();
			if(rs.next()){
				num = rs.getInt(1);
			}
		} catch(Throwable e){  
	           if(connection!=null){  
	               try {  
	            	   DbUtil.rollback(connection);  
	               } catch (SQLException e1) {  
	                   e1.printStackTrace();  
	               }  
	           }  
		}finally{
			DAOFactory.closeAll(connection,ps, rs);
		}
		return num;
	}
	private String getMaxIdSql(TableName tablename, String primary,
			String columnname, String value) {
		// TODO Auto-generated method stub
		StringBuilder sqlstr = new StringBuilder();
		sqlstr.append(" select max(");
		sqlstr.append(primary);
		sqlstr.append(" ) from ");
		sqlstr.append(tablename);
		if(columnname!=null&&value!=null){
			sqlstr.append(" where ");
			sqlstr.append(columnname);
			sqlstr.append(" = ");
			sqlstr.append(value);
		}
		return sqlstr.toString();
	}
	private String getMiniSql(TableName tablename, String primary,
			String columnname, String value) {
		// TODO Auto-generated method stub
//		 select classnotifyid from classnotify where publishtime in(select min(publishtime) from classnotify);

		StringBuilder sqlstr = new StringBuilder();
		sqlstr.append("select ");
		sqlstr.append(primary);
		sqlstr.append(" from ");
		sqlstr.append(tablename);
		sqlstr.append(" where ");
		sqlstr.append("publishtime ");
		sqlstr.append("in(");
		sqlstr.append(" select min(");
		sqlstr.append("publishtime ");
		sqlstr.append(" ) from ");
		sqlstr.append(tablename);
		if(columnname!=null&&value!=null){
			sqlstr.append(" where ");
			sqlstr.append(columnname);
			sqlstr.append(" = ");
			sqlstr.append(value);
		}
		sqlstr.append(")");
		return sqlstr.toString();
	}

	public int getMaxId(Connection connection, TableName tablename,
			String primary,String columnname,String value,String addstr) {
		// TODO Auto-generated method stub
		int maxid = 0;
		PreparedStatement ps=null;
		ResultSet rs=null;
		String sql = getMaxIdSql(tablename,primary,columnname,value);
		if(addstr!=null){
			sql=sql+" "+addstr;
		}
		L.p(T+"getMaxId"+sql);
		try{
			ps=connection.prepareStatement(sql);
			rs=ps.executeQuery();
			L.p("mysql:"+ps.toString());
			 connection.commit();
			if(rs.next()){
				maxid = rs.getInt(1);
			}
		} catch(Throwable e){  
	           if(connection!=null){  
	               try {  
	            	   connection.rollback();  
	               } catch (SQLException e1) {  
	                   e1.printStackTrace();  
	               }  
	           }  
		}finally{
			DAOFactory.closeAll(connection,ps, rs);
		}
		return maxid;
	}
	public int getMiniId(Connection connection, TableName tablename,
			String primary, String columnname, String value, String addstr) {
		// TODO Auto-generated method stub
		int miniid = 0;
		PreparedStatement ps=null;
		ResultSet rs=null;
		String sql = getMiniSql(tablename,primary,columnname,value);
		if(addstr!=null){
			sql=sql+" "+addstr;
		}
		L.p(T+"getMaxId"+sql);
		try{
			ps=connection.prepareStatement(sql);
			rs=ps.executeQuery();
			L.p("mysql:"+ps.toString());
			connection.commit();
			if(rs.next()){
				miniid = rs.getInt(1);
			}
		} catch(Throwable e){  
	           if(connection!=null){  
	               try {  
	            	  DbUtil.rollback(connection);
	            	    
	               } catch (SQLException e1) {  
	                   e1.printStackTrace();  
	               }  
	           }  
		}finally{
			DAOFactory.closeAll(connection,ps, rs);
		}
		return miniid;
	}
	public <T> List<T> getContactsList(Connection con,
			TableName telContactsTable, int schoolcode, String search_key) {
		List<T> list=new ArrayList<T>();
		PreparedStatement ps=null;
		ResultSet rs=null;
		String sql = "select * from "+telContactsTable+" left join "+TableName.IMAGE_TABLE+
				" on ("+TableName.TEL_CONTACTS_TABLE+".imageid ="+TableName.IMAGE_TABLE+".imageid)"+
				" where name like '%"+search_key+"%' or tel like '%"+search_key+"%'";
		try{
			ps=con.prepareStatement(sql);
			L.p("mysql:"+ps.toString());
			rs=ps.executeQuery();
			con.commit();
			while(rs.next()){
				list.add((T)SpecMySql.getPojoBySql(telContactsTable,rs));
			}
			
		}catch(Throwable e){  
	           if(con!=null){  
	               try {  
	                   DbUtil.rollback(con);
	               } catch (SQLException e1) {  
	                   e1.printStackTrace();  
	               }  
	           }  
		}finally{
			DAOFactory.closeAll(con,ps, rs);
		}
		return list;
	}
	
}
